package com.xspace.android.xafcommon.db.dbsub;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.xspace.android.xafcommon.been.OfflineHtmlBean;
import com.xspace.android.xafcommon.db.BaseSql;
import com.xspace.android.xafcommon.util.DateUtils;
import com.xspace.android.xafcommon.util.LogUtil;

import java.util.ArrayList;
import java.util.List;


/**
 * 离线表单
 *
 * @author Administrator
 */
public class OfflineHtmlDB extends BaseSql {
    private final String FormID = "formid";
    private final String Url = "url";//数据提交接口
    private final String HtmlContent = "content";//数据
    private final String HtmlBlob = "htmlblob";
    //	private final String InputData = "data";
    //	private final String InputData1 = "data1";
    private final String UserId = "user_id";
    private final String UrlType = "url_type";
    //	private final String Version = "version";
    /**
     * 0代表单独表单；
     * 1代表相关表单
     */
    private final String Flag = "flag";

    /**
     * 散户 CId
     **/
    private final String ReMark0 = "cid";
    /**
     * 提交失败原因
     */
    private final String ReMark1 = "remark1";
    private final String ReMark2 = "remark2";
    private final String ReMark3 = "remark3";
    private String TableName = "OfflineHtml";
    private MyDBHelper helpDb;
    private SQLiteDatabase sqldb;
    private String tag = "OfflineHtmlDB";

    /**
     * 新添加的字段
     **/
    private String EditUrl = "editUrl";
    private String CommitStatus = "commitStatus";
    private String InsertTime = "insertTime";
    private final String FormName = "formname";

    @Override
    public void create(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TableName + " (_ID INTEGER PRIMARY KEY AUTOINCREMENT," + Url + " text,"
                + HtmlContent + " text," + UserId + " text," + UrlType + " text," + EditUrl + " text," + CommitStatus
                + " varchar(2)," + InsertTime + " text," + FormName + " text," + ReMark0 + " text," + ReMark1 + " text,"
                + ReMark2 + " text," + ReMark3 + " text," + HtmlBlob + " blob," + Flag + "   varchar(2)," + FormID
                + " integer64);");
    }

    public OfflineHtmlDB() {

    }

    public void dropTable(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + TableName);
    }

    public OfflineHtmlDB(MyDBHelper db) {
        super(db);
        helpDb = db;
    }

    @Override
    public void upgrade(SQLiteDatabase arg0) {

    }

    protected void checkDb() {
        if (sqldb == null || !sqldb.isOpen()) {
            sqldb = helpDb.getWritableDatabase();
        }
    }

    public void closeDbAndCursor(Cursor cursor) {
        if (cursor != null) {
            cursor.close();
            cursor = null;
        }
        /*
         * if (db != null) { db.close(); db = null; }
		 */
    }

    /**
     * 方法描述 : 该方法实现的功能描述
     *
     * @param formname
     * @param url
     * @param editUrl
     * @param content
     * @param userId
     * @param flag
     * @param urlType
     * @param cid
     * @return
     */
    public long insertOrUpdate(String formId, String formname, String url, String editUrl, String content,
                               String userId, String flag, String urlType, String cid) {
        if (TextUtils.isEmpty(content) && TextUtils.isEmpty(content))
            return 0;
        long inserttime = 0;
        checkDb();
        ContentValues cv = new ContentValues();
        if (!TextUtils.isEmpty(formId)) {
            String[] columns = {Url, HtmlContent, Flag, FormID};
            String selection = FormID + "=?";
            if (OfflineHtmlBean.SanhuInfo.equals(urlType)) {
                selection = FormID + "=?";
            }
            String[] selectionArgs = new String[]{formId};
            Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
            if (cur != null && cur.getCount() > 0) {
                if (cur.moveToFirst()) {
                    inserttime = cur.getLong(3);
                }
                if (!TextUtils.isEmpty(formname)) {
                    cv.put(FormName, formname);
                }
                if (!TextUtils.isEmpty(url)) {
                    cv.put(Url, url);
                }
                if (!TextUtils.isEmpty(editUrl)) {
                    cv.put(EditUrl, editUrl);
                }
                if (!TextUtils.isEmpty(content)) {
                    cv.put(HtmlContent, content);
                }
                if (!TextUtils.isEmpty(userId)) {
                    cv.put(UserId, userId);
                }
                if (!TextUtils.isEmpty(flag)) {
                    cv.put(Flag, flag);
                }
                if (!TextUtils.isEmpty(urlType)) {
                    cv.put(UrlType, urlType);
                }
                if (!TextUtils.isEmpty(cid)) {
                    cv.put(ReMark0, cid);
                }
                sqldb.update(TableName, cv, selection, selectionArgs);
                LogUtil.v(tag, "insertOrUpdate update>>>" + content + " formId:" + inserttime);
                closeDbAndCursor(cur);
                LogUtil.v(tag, "id不存在，修改成功");
            } else {
                LogUtil.v(tag, "id不存在，修改失败");
            }
        } else {
            LogUtil.v(tag, "插入数据");
            inserttime = System.currentTimeMillis();
            cv.put(FormName, formname);
            cv.put(Url, url);
            cv.put(EditUrl, editUrl);
            cv.put(HtmlContent, content);
            cv.put(UserId, userId);
            cv.put(Flag, flag);
            cv.put(UrlType, urlType);
            cv.put(ReMark0, cid);
            cv.put(FormID, inserttime);
            cv.put(CommitStatus, "1");
            cv.put(InsertTime, DateUtils.getCurrentDatetimeString(""));
            long result = sqldb.insert(TableName, null, cv);
            LogUtil.v(tag, "insertOrUpdate insert>>>Url:" + url + " HtmlContent:" + content + " result:" + result
                    + " urlType " + urlType + " cid:" + cid + " flag:" + flag + "  InsertTime:" + InsertTime);
        }

        return inserttime;
    }

    /**
     *
     * @param UrlStr
     * @param inputData
     * @param content
     * @param version
     * @param userid
     * @param type  urlType
     * @param cid  散户id
     */
    //	public long insertOrUpdate(String UrlStr, String inputData, String content, String version, String userid,
    //			String utype, String cid, String flag) {
    //		if (TextUtils.isEmpty(content) && TextUtils.isEmpty(inputData))
    //			return 0;
    //		long inserttime = 0;
    //		openDB();
    //		ContentValues cv = new ContentValues();
    //		String[] columns = { Url, HtmlContent, InputData, Version, Flag, FormID };
    //		String selection = Url + "=? and " + UserId + "=? and " + UrlType + "=? and " + Flag + "=? and 1=2";
    //		if (OfflineHtmlBean.SanhuInfo.equals(utype)) {
    //			selection = Url + "=? and " + UserId + "=? and " + UrlType + "=? and " + ReMark0 + "=?";
    //		}
    //		String[] selectionArgs = new String[] { UrlStr, userid, utype, flag };
    //		Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
    //		if (cur != null && cur.getCount() > 0) {
    //			if (cur.moveToFirst()) {
    //				inserttime = cur.getLong(5);
    //			}
    //			if (!TextUtils.isEmpty(content))
    //				cv.put(HtmlContent, content);
    //
    //			if (!TextUtils.isEmpty(inputData))
    //				cv.put(InputData, inputData);
    //
    //			if (!TextUtils.isEmpty(version))
    //				version = "1.0";
    //			cv.put(Version, version);
    //
    //			cv.put(UserId, userid);
    //			cv.put(UrlType, utype);
    //			cv.put(ReMark0, cid);
    //			sqldb.update(TableName, cv, selection, selectionArgs);
    //			LogUtil.v(tag, "insertOrUpdate update>>>" + content + " inputData:" + inputData);
    //		} else {
    //			inserttime = System.currentTimeMillis();
    //			cv.put(Url, UrlStr);
    //			cv.put(HtmlContent, content);
    //			cv.put(InputData, inputData);
    //			cv.put(Version, version);
    //			cv.put(UserId, userid);
    //			cv.put(UrlType, utype);
    //			cv.put(ReMark0, cid);
    //			cv.put(Flag, flag);
    //			cv.put(FormID, inserttime);
    //			long result = sqldb.insert(TableName, null, cv);
    //			LogUtil.v(tag, "insertOrUpdate insert>>>Url:" + UrlStr + " HtmlContent:" + content + " InputData:"
    //					+ inputData + " result:" + result + " " + utype + " cid:" + cid + " flag:" + flag);
    //		}
    //		cur.close();
    //		return inserttime;
    //	}

    /**
     *
     * @param UrlStr
     * @param inputData
     * @param content
     * @param version
     * @param userid
     * @param type  urlType
     * @param cid  散户id
     */
    //	public long insertOrUpdate(String UrlStr, String inputData, String content, String version, String userid,
    //			String utype, String cid, String flag, String remark_status) {
    //		if (TextUtils.isEmpty(content) && TextUtils.isEmpty(inputData))
    //			return 0;
    //		long inserttime = 0;
    //		openDB();
    //		ContentValues cv = new ContentValues();
    //		String[] columns = { Url, HtmlContent, InputData, Version, Flag, FormID };
    //		String selection = Url + "=? and " + UserId + "=? and " + UrlType + "=? and " + Flag + "=? and 1=2";
    //		if (OfflineHtmlBean.SanhuInfo.equals(utype)) {
    //			selection = Url + "=? and " + UserId + "=? and " + UrlType + "=? and " + ReMark0 + "=?";
    //		}
    //		String[] selectionArgs = new String[] { UrlStr, userid, utype, flag };
    //		Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
    //		if (cur != null && cur.getCount() > 0) {
    //			if (cur.moveToFirst()) {
    //				inserttime = cur.getLong(5);
    //			}
    //			if (!TextUtils.isEmpty(content))
    //				cv.put(HtmlContent, content);
    //
    //			if (!TextUtils.isEmpty(inputData))
    //				cv.put(InputData, inputData);
    //
    //			if (!TextUtils.isEmpty(version))
    //				version = "1.0";
    //			cv.put(Version, version);
    //
    //			cv.put(UserId, userid);
    //			cv.put(UrlType, utype);
    //			cv.put(ReMark0, cid);
    //			cv.put(ReMark1, remark_status);
    //			sqldb.update(TableName, cv, selection, selectionArgs);
    //			LogUtil.v(tag, "insertOrUpdate update>>>" + content + " inputData:" + inputData);
    //		} else {
    //			inserttime = System.currentTimeMillis();
    //			cv.put(Url, UrlStr);
    //			cv.put(HtmlContent, content);
    //			cv.put(InputData, inputData);
    //			cv.put(Version, version);
    //			cv.put(UserId, userid);
    //			cv.put(UrlType, utype);
    //			cv.put(ReMark0, cid);
    //			cv.put(Flag, flag);
    //			cv.put(FormID, inserttime);
    //			cv.put(ReMark1, remark_status);
    //			long result = sqldb.insert(TableName, null, cv);
    //			LogUtil.v(tag,
    //					"insertOrUpdate insert>>>Url:" + UrlStr + " HtmlContent:" + content + " InputData:" + inputData
    //							+ " result:" + result + " " + utype + " cid:" + cid + " flag:" + flag + "ReMark1 "
    //							+ ReMark1);
    //		}
    //		cur.close();
    //		return inserttime;
    //	}

    /**
     * 查询离线表单html或者输入内容
     *
     * @param urlStr
     * @param type   1 html 2inputdata 3 remark0
     * @return
     */
    public String queryHtmlData(String urlStr, int type, String userid) {
        String content = null;
        if (type < 1 || type > 2)
            return content;
        checkDb();
        String[] columns = {Url, HtmlContent, ReMark0};
        String selection = Url + "=? and " + UserId + " =?";
        String[] selectionArgs = new String[]{urlStr, userid};
        Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
        if (cur != null && cur.getCount() > 0) {
            if (cur.moveToFirst()) {
                content = cur.getString(type);
            }
        }
        cur.close();
        LogUtil.v(tag, "queryHtmlData content:" + content + " type:" + type);
        return content;
    }

    /**
     * @param userid
     * @param flag   0独立表单 1日志相关表单
     * @return
     */
    public List<OfflineHtmlBean> queryDataByUserid(String userid, String flag) {
        checkDb();
        String selection = UserId + " =? and " + Flag + "=? and " + CommitStatus + " is not 0";
        String[] selectionArgs = new String[]{userid, flag};
        List<OfflineHtmlBean> list = new ArrayList<OfflineHtmlBean>();
        String[] columns = {Url, HtmlContent, UserId, UrlType, ReMark0, FormID};
        Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
        int count = 0;
        if (cur != null && cur.getCount() > 0) {
            count = cur.getCount();
            while (cur.moveToNext()) {
                String urlstr = cur.getString(0);
                String htmlcontent = cur.getString(1);
                String userid0 = cur.getString(2);
                String urltype = cur.getString(3);
                String cid = cur.getString(4);
                String formId = cur.getString(5);
                LogUtil.v(tag, "queryDataByUserid url:" + urlstr + " htmlcontent:" + htmlcontent + " userid:" + userid0
                        + " cid:" + cid + " urltype:" + urltype);
                OfflineHtmlBean bean = new OfflineHtmlBean(htmlcontent, urlstr, formId);
                bean.urlType = urltype;
                bean.userid = userid0;
                bean.sanhuid = cid;
                list.add(bean);
            }
        }
        cur.close();
        LogUtil.v(tag, "queryDataByUserid count:" + count);
        return list;
    }

    //	/**
    //	 *
    //	 * @param userid
    //	 * @param flag 0独立表单 1日志相关表单
    //	 * @return
    //	 */
    //	public List<OfflineHtmlBean> queryDataByUserid(String userid, String flag, String remark_status) {
    //		checkDb();
    //		String selection = UserId + " =? and " + Flag + "=? and " + ReMark1 + "=? and " + ReMark2 + " is not 0";
    //		String[] selectionArgs = new String[] { userid, flag, remark_status };
    //		List<OfflineHtmlBean> list = new ArrayList<OfflineHtmlBean>();
    //		String[] columns = { Url, HtmlContent, InputData, Version, UserId, UrlType, ReMark0, FormID };
    //		Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
    //		int count = 0;
    //		if (cur != null && cur.getCount() > 0) {
    //			count = cur.getCount();
    //			while (cur.moveToNext()) {
    //				String urlstr = cur.getString(0);
    //				String htmlcontent = cur.getString(1);
    //				String inputdata = cur.getString(2);
    //				String verison = cur.getString(3);
    //				String userid0 = cur.getString(4);
    //				String urltype = cur.getString(5);
    //				String cid = cur.getString(6);
    //				String formId = cur.getString(7);
    //				LogUtil.v(tag, "queryDataByUserid url:" + urlstr + " htmlcontent:" + htmlcontent + " verison:" +
    // verison
    //						+ " userid:" + userid0 + " cid:" + cid + " urltype:" + urltype);
    //				OfflineHtmlBean bean = new OfflineHtmlBean(htmlcontent, inputdata, urlstr, formId);
    //				bean.urlType = urltype;
    //				bean.userid = userid0;
    //				bean.sanhuid = cid;
    //				list.add(bean);
    //			}
    //		}
    //		cur.close();
    //		LogUtil.v(tag, "queryDataByUserid count:" + count);
    //		return list;
    //	}

    //	public void DeleteOneUrlData(String userid, String urlType) {
    //		checkDb();
    //		String whereClause = UserId + " =? and " + UrlType + "=?";
    //		String[] whereArgs = new String[] { userid, urlType };
    //		int result = sqldb.delete(TableName, whereClause, whereArgs);
    //		LogUtil.v(tag, "DeleteOneData userid:" + userid + " urlType:" + urlType + " result:" + result);
    //	}

    public void DeleteOneSanhuData(String userid, String urlType, String cid) {
        checkDb();
        String whereClause = UserId + " =? and " + UrlType + "=? and " + ReMark0 + "=?";
        String[] whereArgs = new String[]{userid, urlType, cid};
        int result = sqldb.delete(TableName, whereClause, whereArgs);
        LogUtil.v(tag,
                "DeleteOneSanhuData userid:" + userid + " urlType:" + urlType + " result:" + result + " cid:" + cid);
    }

    //	public void DeleteOneData(String userid) {
    //		checkDb();
    //		String whereClause = UserId + " =?";
    //		String[] whereArgs = new String[] { userid };
    //		int result = sqldb.delete(TableName, whereClause, whereArgs);
    //		LogUtil.v(tag, "DeleteOneData userid:" + userid + " result:" + result);
    //	}

    public void DeleteOneDataByFormId(String formId) {
        checkDb();
        String whereClause = FormID + " =?";
        String[] whereArgs = new String[]{formId};
        int result = sqldb.delete(TableName, whereClause, whereArgs);
        LogUtil.v(tag, "DeleteOneData formId:" + formId + " result:" + result);
    }

    public void updateFailByFormId(String formId, String cause) {
        checkDb();
        String whereClause = FormID + " =? ";
        String[] whereArgs = new String[]{formId};
        ContentValues cv = new ContentValues();
        cv.put(CommitStatus, "0");
        if (!TextUtils.isEmpty(cause)) {
            cv.put(ReMark1, cause);
        }
        int result = sqldb.update(TableName, cv, whereClause, whereArgs);
        LogUtil.v(tag, "更新表单formid为" + formId + "的表单为失败状态，结果为" + result + ">>>原因" + cause);
    }

//    /**
//     * 离线html zip的存储
//     * @param urltype zip
//     * @param version
//     * @param data
//     */
//    public void insertOrUpdateBlobData(String urltype, String version, byte[] data) {
//        checkDb();
//        ContentValues cv = new ContentValues();
//        String[] columns = {Url, HtmlContent, InputData, Version};
//        String selection = UrlType + "=? ";
//
//        String[] selectionArgs = new String[]{urltype};
//        Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
//        if (cur != null && cur.getCount() > 0) {
//            if (data != null)
//                cv.put(HtmlBlob, data);
//            if (TextUtils.isEmpty(urltype))
//                version = "1.0";
//            cv.put(Version, version);
//
//            sqldb.update(TableName, cv, selection, selectionArgs);
//            LogUtil.v(tag, "insertOrUpdate update>>>" + version + " urltype:" + urltype + " data:" + data.length);
//        } else {
//            cv.put(HtmlBlob, data);
//            cv.put(Version, version);
//            cv.put(UrlType, urltype);
//            long result = sqldb.insert(TableName, null, cv);
//            LogUtil.v(tag, "insertOrUpdate insert>>>UrlType:" + urltype + " HtmlBlob:" + data.length + " Version:"
//                    + version + " result:" + result);
//        }
//        cur.close();
//    }

//    /**
//     *
//     * @param urlStr zip
//     * @return
//     */
//    public byte[] queryHtmlBlobData(String urlStr) {
//        byte[] data = null;
//        checkDb();
//        String[] columns = {Url, HtmlBlob, Version};
//        String selection = UrlType + "=? ";
//        String[] selectionArgs = new String[]{urlStr};
//        Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
//        if (cur != null && cur.getCount() > 0) {
//            if (cur.moveToFirst()) {
//                String version = cur.getString(2);
//                data = cur.getBlob(1);
//                if (data != null)
//                    LogUtil.v(tag, "queryHtmlBlobData size:" + data.length + " version:" + version);
//            }
//        }
//        cur.close();
//        LogUtil.v(tag, "queryHtmlBlobData urlStr:" + urlStr);
//        return data;
//    }

    public OfflineHtmlBean queryWorkLogRelatativeOneData(String formid, String flag, String userid) {
        OfflineHtmlBean bean = null;
        checkDb();
        String selection = FormID + " =? and " + Flag + "=? and " + UserId + "=?";
        String[] selectionArgs = new String[]{formid, flag, userid};
        String[] columns = {Url, HtmlContent, UserId, UrlType, ReMark0, FormID};
        Cursor cur = sqldb.query(TableName, columns, selection, selectionArgs, null, null, null);
        int count = 0;
        if (cur != null && cur.getCount() > 0) {
            count = cur.getCount();
            if (cur.moveToNext()) {
                String urlstr = cur.getString(0);
                String htmlcontent = cur.getString(1);
                String userid0 = cur.getString(2);
                String urltype = cur.getString(3);
                String cid = cur.getString(4);
                String formId = cur.getString(5);
                LogUtil.v(tag, "queryWorkLogRelatativeOneData url:" + urlstr + " htmlcontent:" + htmlcontent
                        + " userid:" + userid0 + " cid:" + cid + " urltype:" + urltype);
                bean = new OfflineHtmlBean(htmlcontent, urlstr, formId);
                bean.urlType = urltype;
                bean.userid = userid0;
            }
        }

        closeDbAndCursor(cur);
        LogUtil.v(tag, "queryWorkLogRelatativeOneData count:" + count);
        return bean;
    }

    /**
     * 方法描述 : 根据formId删除删除对应表单数据
     *
     * @param formId
     */
    public void deleteRowByFormId(String formId) {
        checkDb();
        String selection = FormID + " = ? ";
        String[] selectionArgs = new String[]{formId};
        int result = sqldb.delete(TableName, selection, selectionArgs);
        LogUtil.d(tag, "删除表单 formId=" + formId + ">>>result=" + result);
    }

    public int deleteAllRows() {
        checkDb();
        int result = sqldb.delete(TableName, null, null);
        LogUtil.v(tag, "DeleteAllRows result:" + result);
        return result;
    }

    /**
     * 删除所有与工作日志有关的相关表单
     *
     * @return
     */
    public int deleteAllLogRows() {
        checkDb();
        String selection = Flag + " = ? ";
        String[] selectionArgs = new String[]{"1"};
        int result = sqldb.delete(TableName, selection, selectionArgs);
        LogUtil.v(tag, "DeleteAllLogRows result:" + result);
        return result;
    }

}
